package dao

import (
	"GraduationProject/tools"
	"database/sql"
	"fmt"
	"strconv"
)

//==================================================添加================================================================

//根据学生id,课程id产生关联

func AddCourseBySidAndCid(Sid string, Cid string) (NumRows int64) {
	sql, err := sql.Open(DriverName, DataSourceNameInit(Db_Course_select_sys))
	defer sql.Close()
	CheckErr(err)
	stamp, _ := tools.GetTimeStamp()
	Cid_int64, err := strconv.ParseInt(Cid, 10, 64) //课程id转int64
	Sid_int64, err := strconv.ParseInt(Sid, 10, 64) //课程id转int64
	CheckErr(err)

	sqlCmd := fmt.Sprintf("insert %s (sc_id,c_id,sd_id)  value (%d,%d,%d); ", Tb_Stud_course, stamp/100, Cid_int64, Sid_int64)
	prepare, err := sql.Prepare(sqlCmd)
	result, err := prepare.Exec()
	rowsAffected, err := result.RowsAffected()

	return rowsAffected
}

//==================================================删除================================================================

//根据学生id以及退课的课程id，删除课程

func StudExitCourseBySid(sdId string, Cid string) (RowsNum int64) {

	sql, err := sql.Open(DriverName, DataSourceNameInit(Db_Course_select_sys))
	CheckErr(err)
	defer sql.Close()
	cId_int64, err := strconv.ParseInt(Cid, 10, 64)
	sdId_int64, err := strconv.ParseInt(sdId, 10, 64)
	delSql := fmt.Sprintf("delete from stud_course sc where sc.sd_id = %d and sc.c_id = %d", sdId_int64, cId_int64)
	stmt, err := sql.Prepare(delSql)

	CheckErr(err)
	exec, err := stmt.Exec()

	num, err := exec.RowsAffected()

	CheckErr(err)
	return num
}

//==================================================查询================================================================

//根据学生id查询选课表

func QueryStudCourseBySid(Sid string) StudCourse {

	sc := StudCourse{}

	db, err := sql.Open(DriverName, DataSourceNameInit(Db_Course_select_sys))
	CheckErr(err)
	defer db.Close()

	SidInt64, _ := strconv.ParseInt(Sid, 10, 64)

	sqlCmd := fmt.Sprintf("select * from %s sc where sc.Sd_id=%d", Tb_Stud_course, SidInt64)
	rows, err := db.Query(sqlCmd)

	for rows.Next() {
		rows.Scan(&sc.SC_id, &sc.C_id, &sc.Sd_id, &sc.Sd_Sscore)
	}

	return sc
}

// 根据学生id查询选择的课程

func QueryCourseByStudId(id string, searchData string, searchPage int) (map[int]StudCourses, int, error) {
	// 建立数据库连接
	db, err := sql.Open(DriverName, DataSourceNameInit(Db_Course_select_sys))
	if err != nil {
		return nil, 0, fmt.Errorf("failed to open database connection: %v", err)
	}
	defer db.Close() // 确保在函数返回前关闭数据库连接

	// 将 id 转换为 int64 类型
	idInt64, err := strconv.ParseInt(id, 10, 64)
	if err != nil {
		return nil, 0, fmt.Errorf("failed to parse student id: %v", err)
	}

	// 每页显示的信息数和当前页面
	everyPageShows := ShowNumbersProxy()
	currentPage := searchPage * everyPageShows

	// 准备 SQL 查询语句
	countSql := fmt.Sprintf(`
        SELECT COUNT(*) 
        FROM (
            SELECT c.c_id
            FROM stud_del sd
            JOIN stud_course sc ON sc.sd_id = sd.sd_id
            JOIN course c ON sc.c_id = c.c_id
            WHERE sc.sd_id = ? AND (c.c_name LIKE ? OR c.c_teach LIKE ?)
            GROUP BY c.c_id
        ) AS subquery
    `)
	sqlcmd := fmt.Sprintf(`
    SELECT c.c_id, c.c_name, c.c_teach, sc.Sd_Sscore, c.c_Tscore as SC_Tscore
    FROM stud_del sd
    JOIN stud_course sc ON sc.sd_id = sd.sd_id
    JOIN course c ON sc.c_id = c.c_id
    WHERE sc.sd_id = ? AND (c.c_name LIKE ? OR c.c_teach LIKE ?)
    GROUP BY c.c_id, c.c_name, c.c_teach, sc.Sd_Sscore, c.c_Tscore
    ORDER BY c.c_name ASC
    LIMIT ?, ?
`)

	// 查询数据库获取课程列表
	sprintfSql := fmt.Sprintf(sqlcmd, idInt64, "%"+searchData+"%", "%"+searchData+"%", currentPage, everyPageShows)
	fmt.Println("++++++++++++++++++++++++++>>>>>>>>>" + sprintfSql)
	rows, err := db.Query(sqlcmd, idInt64, "%"+searchData+"%", "%"+searchData+"%", currentPage, everyPageShows)
	if err != nil {
		return nil, 0, fmt.Errorf("failed to query database: %v", err)
	}
	defer rows.Close()

	courseList := make(map[int]StudCourses)
	count := 0

	// 遍历查询结果，填充课程列表
	for rows.Next() {
		var course StudCourses
		if err := rows.Scan(&course.SC_id, &course.SC_name, &course.SC_teach, &course.SC_Sscore, &course.SC_Tscore); err != nil {
			return nil, 0, fmt.Errorf("failed to scan row: %v", err)
		}
		courseList[count] = course
		count++
	}

	// 查询总记录数
	var totalCount int
	if err := db.QueryRow(countSql, idInt64, "%"+searchData+"%", "%"+searchData+"%").Scan(&totalCount); err != nil {
		return nil, 0, fmt.Errorf("failed to get total count: %v", err)
	}

	// 计算总页数
	countPage := (totalCount + everyPageShows - 1) / everyPageShows

	return courseList, countPage, nil
}

//跟据学生id ，课程id查询课程

func GetStudCourseBySidAndCid(Sid string, Cid string) (finished int64) {
	db, err := sql.Open(DriverName, DataSourceNameInit(Db_Course_select_sys))
	CheckErr(err)
	defer db.Close()

	SidInt64, _ := strconv.ParseInt(Sid, 10, 64)
	CidInt64, _ := strconv.ParseInt(Cid, 10, 64)

	sqlCmd := fmt.Sprintf("select * from %s sc where sc.c_id=%d and sc.sd_id=%d", Tb_Stud_course, CidInt64, SidInt64)
	rows, err := db.Query(sqlCmd)

	for rows.Next() {
		return 1
	}

	return 0
}

//==================================================修改================================================================

//【管理员】 给学生的选课评分

func GradesBySidAndCid(sid string, cid string, score float64) (finished int64) {

	Step := GetStudCourseBySidAndCid(sid, cid)

	//当查询不到
	if Step == 0 {
		return 0
	}

	db, err := sql.Open(DriverName, DataSourceNameInit(Db_Course_select_sys))
	CheckErr(err)
	defer db.Close()

	SidInt64, _ := strconv.ParseInt(sid, 10, 64)
	CidInt64, _ := strconv.ParseInt(cid, 10, 64)

	sqlCmd := fmt.Sprintf("UPDATE stud_course sc set sc.sd_Sscore=%.2f where sc.c_id=%d  and sc.sd_id=%d", score, CidInt64, SidInt64)
	stmt, err := db.Prepare(sqlCmd)
	CheckErr(err)

	exec, err := stmt.Exec()
	CheckErr(err)

	Finished, err := exec.RowsAffected()

	return Finished + Step
}
